# Import resources
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns
from datetime import datetime, timedelta
import pickle
from scipy import stats
import warnings
warnings.filterwarnings("ignore")
# Display all columns in dataframe
pd.set_option('display.max_columns', None)
Denormalized table with information from other tables.
The goal of this notebook is to do some basic feature engineering and data cleaning. Data engineering and data cleaning are iterative processes that precede and then continue along with data analysis, as we often need to go back upon learning more about the dataset. This notebook is a combination and somewhat simplication of that process, and it is not exhaustive.
It is composed of four parts:
1. Loading a basic cleaning and engineering of original dataset.
2.3.4. Loading, cleaning, and joining in datasets that enrich the original content, with: geographical, demographical, and air industry information about the countries and cities for the departure and destination locations of each entry.
Load dataset, remove duplicates, check for null values
time varibles to Python datetime
Separating_location_from_the_city_for_origin_and_destination
A) 'Use frequency' to be categorical: 'once', 'more than once'
B) Outcome of the search for Hopper: 'gained', 'lost', 'expected'
E) Whether a search is a search or a watch
F) Difference between lowest price and price at first buy notification
G) How many days in advance of the departure is the user searching for this trip
Merge datasets geo and df, based on the origin_city and the destination_city
Get country and state/province
Domestic or international flight
Get population in 2018, aggregate by age groups older than 15, and by gender.
Merge pop on original + geo dataset
df = pd.read_csv('WatchesTable.csv')
df.head(6)
df.shape
df = df.drop_duplicates(subset=None, keep='last', inplace=False)
df.shape
df.head(6)
for i,c in enumerate(df.columns):
nulls = np.sum(df[c].isnull())
if nulls > 0:
print(c + " " + np.str(nulls))
df.sample(4)
# Asign unique identifier to each row
df['transaction_id'] = df.index
# Move the transaction_id column to the front
def col_to_position(df, column, index):
"""
df: dataframe in question
column: string, name of the column to be moved
index: int, position in which you want column
"""
cols = df.columns.tolist()
cols.insert(index, cols.pop(cols.index(column)))
df = df.reindex(columns= cols)
return df
df = col_to_position(df, 'transaction_id', 0)
df.sample(4)
# Checking the types of data
df.dtypes
# Converting time variables to datetime Python type
df.departure_date = pd.to_datetime(df.departure_date)
df.return_date = pd.to_datetime(df.return_date)
df.first_search_dt = pd.to_datetime(df.first_search_dt)
df.watch_added_dt = pd.to_datetime(df.watch_added_dt)
df.latest_status_change_dt = pd.to_datetime(df.latest_status_change_dt)
df.first_buy_dt = pd.to_datetime(df.first_buy_dt)
df.last_notif_dt = pd.to_datetime(df.last_notif_dt)
df.forecast_last_warning_date = pd.to_datetime(df.forecast_last_warning_date)
df.forecast_last_danger_date = pd.to_datetime(df.forecast_last_danger_date)
# pandas._libs.tslibs.nattype.NaTType
# Separating location from the city for "origin" and "destination"
df['origin_type'], df['origin_city'] = df.origin.str.split('/').str
df['destination_type'], df['destination_city'] = df.destination.str.split('/').str
df = col_to_position(df, 'origin_city', 1)
df = col_to_position(df, 'destination_city', 2)
# Drop 'origin' and 'destination' columns
df = df.drop(['origin','destination'], axis =1)
df.sample(4)
## A) 'Use frequency' to be categorical: 'once', 'more than once'
# Make value counts into a dictionary
dict_counts = df['user_id'].value_counts().to_dict()
# Map user_id values to uniqueness by using dictionary of counts
df['Use frequency'] = df['user_id'].map(dict_counts)
df.sample(4)
## B) Outcome of the search for Hopper: 'gained', 'lost', 'expected'
df['outcome'] = 'lost'
df['outcome'][(df['status_latest'] == 'shopped') | (df['status_latest'] == 'booked')] = 'gained'
df['outcome'][df['status_latest'] == 'active'] = 'expected'
# PREP for C) and D)
# Get subset of table with users that have multiple searches
tb = df[df['Use frequency'] != 1].copy()
tb = tb.sort_values(by=['user_id','first_search_dt'])
# Initialize new columns
def new_columns_C_D(df):
""" Give default values to columns to
be filled accomplishing tasks C and D.
Input - dataframe to have columns added
Output - dataframe with added columns
"""
df['ordered'] = 1
df['session'] = np.int64(1)
df['diff_day'] = np.int64(0)
df['diff'] = pd.Timedelta('0 days 00:00:00')
return df
tb = new_columns_C_D(tb)
## D) Groups of searches done by customer: first five searches were
# done on one day -> 1, the next two were done on a different day ->2,
# the eight was done on a day with no other ->3; and so on.
def clump_sessions(df):
""" For dataframe df, assuming that belongs to a unique user.
Split rows in groups such that those that have taken place in
the same day are assigned a common session number.
"""
if df.user_id.unique().shape[0] != 1:
print("ERROR, too many user_ids")
# Casting "first_search_dt" in case it is still a string
df.first_search_dt = pd.to_datetime(df.first_search_dt)
# Reset the indices of the dataframe
df = df.reset_index(drop=True)
# Substract timestamp difference between present day and day before
dum_row = df['first_search_dt'].shift(periods=1,fill_value=df['first_search_dt'][0])
df['diff'] = df['first_search_dt'] - dum_row
df['diff_day'] = df['diff'].apply(lambda x: x.days)
# Loop and if difference > 1 day, increase the session[i]
for i in range(1,df.shape[0]):
if df['diff_day'][i] > 0:
df['session'][i] = df['session'][i-1] + 1
elif df['diff_day'][i] == 0:
df['session'][i] = df['session'][i-1]
else:
print("ERROR: Dates are not sorted")
return df['session'],df['diff_day'],df['diff']
## Loop for C) and D)
# Loop for all subsets with the same user_id
# and find C) and D) .
# Using the counter as the index marker, and since the dataframe is
# sorted by user_id and first_search_dt,the counter skips ahead
# after each subset of a same user is analyzed.
tb = tb.sort_values(by=['user_id','first_search_dt'])
tb = tb.reset_index(drop=True)
i = 0
while i < tb.shape[0]:
# Get the user_id at this index
user_id = tb['user_id'][i]
print(i)
print(user_id + "\n")
# All the searches done by this user
total_user_searches = np.sum(tb['user_id'] == user_id)
# Index + number of searches done by this user
end = i + total_user_searches
# C) Number of search by that customer: for this same user_id, this given search is their
# 1st, 2nd, 10th.
tb['ordered'][i:end] = list(range(1,total_user_searches + 1))
# D)
dummy = tb[tb['user_id']==user_id]
a,b,c = clump_sessions(dummy)
tb['session'][i:end] = a
tb['diff_day'][i:end] = b
tb['diff'][i:end] = c
# Update counter to new user ahead
i = end